# %%
#!C:\Users\xu\AppData\Local\Programs\Python\Python310
# -*- coding: <utf-8> -*-
#! 本程序功能: 操作Orcad导出的BOM文件，将同一元器件的行合并到一行中
# ~ 思路: 以Value和PCB foorprint列为参考依据，这两列都相同的视为同一元器件
# ? 使用:
# ? 1.在Cadence Orcad中导出BOM，表头格式必须为: Item Quantity Reference Part PCB Footprint MPN
# ? 1.在Cadence Orcad中导出BOM，需选中Place each part entry on a separate line
# ? 3.手动修改BOM文件路径，注意此文件后缀名为.BOM，是Cadence Orcad导出的原始BOM文件，无需手动另存为.xlsx


# %%
import openpyxl
import pyautogui
import re
import sys
import os
import time
import pyperclip
import win32com.client as win32

# BOM文件路径
bom_path = r'C:\Users\xu\Desktop\ESCB\ESCB-V3_CORE_ESCB-V3.BOM'

# 表头格式形如: Item Quantity Reference Part PCB Footprint MPN
std_bom_header = ('Item', 'Quantity', 'Reference', 'Part', 'PCB Footprint', 'MPN')

# %%
# 获取当前时间戳
timestamp = time.strftime("%Y%m%d%H%M%S", time.localtime())
print(f"当前时间戳: {timestamp}")

# 将 .bom 文件另存为 .xlsx 文件
bom_to_excel_file_path = bom_path + '.xlsx'

# 以下程序段功能：通过调用excel将.bom另存为.xlsx
# 创建 Excel 应用程序对象
excel = win32.Dispatch("Excel.Application")

# 禁用提示
excel.DisplayAlerts = False

# 打开 .bom 文件
workbook = excel.Workbooks.Open(bom_path)

# 将 .bom 文件另存为 .xlsx 文件
workbook.SaveAs(bom_to_excel_file_path, FileFormat=51)  # 51 表示 .xlsx 格式

# 关闭工作簿
workbook.Close()

# 启用提示
excel.DisplayAlerts = True

# 退出 Excel 应用程序
excel.Quit()

# %%
def get_sheet_and_bom_name(old_bom_excel_path):
    bom_to_excel_folder_path = os.path.dirname(old_bom_excel_path)
    bom_to_excel_filename = os.path.splitext(os.path.basename(old_bom_excel_path))[0]

    # 最终的sheet名, 长度不能超过31字符，否则excel打开会报警告
    final_sheet_title = (bom_to_excel_filename + '-' + timestamp)[:31]
    # 新的表格只能以.xlsx为后缀
    final_bom_name = bom_to_excel_folder_path + '\\' + bom_to_excel_filename + '-' + timestamp + '.xlsx'

    return final_sheet_title, final_bom_name

final_sheet_title, final_bom_name = get_sheet_and_bom_name(bom_to_excel_file_path)
print(f'最终sheet名: {final_sheet_title}')
print(f'最终excel文件路径: {final_bom_name}')

# 复制文件路径到剪贴板，以便在下一个程序中粘贴
pyperclip.copy(final_bom_name)

# 打开Excel文件
workbook = openpyxl.load_workbook(bom_to_excel_file_path)
origin_sheet = workbook.active

# %%
# 寻找表头，元器件信息应在表头的后面行
std_bom_header_rom_num = 0
std_bom_header_is_not_found = True
for row in origin_sheet.iter_rows(values_only=True):
    std_bom_header_rom_num += 1
    if row == std_bom_header:
        print(f'表头在第{std_bom_header_rom_num}行，为{row}')
        std_bom_header_is_not_found = False
        break

if std_bom_header_is_not_found:
    pyautogui.alert('未发现标准表头，程序将退出!', '确认')
    sys.exit()

# %%
# 找到元器件起始行，跳过下划线行
bom_components_begin_row_num = std_bom_header_rom_num + 1
for row in origin_sheet.iter_rows(min_row=std_bom_header_rom_num + 1, values_only=True):
    if (row[0] == 1) and type(row[1]) == int:
        bom_components_begin_row_num += 1
        print(f'元器件起始于第{bom_components_begin_row_num}行，为{row}')
        break


# %%
# 存储错误行的序号
error_row_num = bom_components_begin_row_num
# 位号不允许重复 存储位号
reference_ls = []
# 检查BOM表是否出错，前两列应全为空或全为数字，第三列应为序号，应是字母+数字的形式且不能重复，第四列为Value和第五列PCB Footprint应不为空
is_valid_reference_pattern = r"^[A-Za-z]+\d+$"
for row in origin_sheet.iter_rows(min_row=bom_components_begin_row_num, values_only=True):
    if any(row):  # 判断整行是否存在非空值，为空则跳过
        if ((type(row[0]) == int and type(row[1]) == int) or (row[0] == None and row[1] == None)) == False:
            pyautogui.alert(f'第{error_row_num}行={row}的前两列格式错误，非空，也非数字', '提示')
            sys.exit()
        if (re.match(is_valid_reference_pattern, row[2]) == False):
            pyautogui.alert(f'第{error_row_num}行={row}的前三列格式错误，并非位号', '提示')
            sys.exit()
        elif (row[2].strip() in reference_ls):
            pyautogui.alert(f'第{error_row_num}行={row}的位号重复', '提示')
            sys.exit()
        else:
            reference_ls.append(row[2].strip())
        if row[3] == None:
            pyautogui.alert(f'第{error_row_num}行={row}的前四列格式错误, Value缺失', '提示')
            sys.exit()
        if row[4] == None:
            pyautogui.alert(f'第{error_row_num}行={row}的前五列格式错误, PCB Footprint缺失', '提示')
            sys.exit()
        error_row_num += 1

# pyautogui.alert('此BOM表格式正确!', '确认')

# %%
# 此单元用于标准化电容电阻等元器件的value值
def convert_capacitance_unit(value):
    cap_unit_ls = ['mF', 'uF', 'nF', 'pF', 'fF']
    cap_value = 0.0
    index = 0
    cap_other = ''
    for j in range(len(cap_unit_ls)):
        for i in range(len(value)):
            if value[i : i+2] == cap_unit_ls[j]:
                cap_value = float(value[:i])
                index = j
                cap_other = value[i+2:]
                break
        if cap_value != 0.0:
            break
    if cap_value >= 1000:
        cap_value = cap_value / 1000
        index = index - 1
    elif cap_value < 1:
        cap_value = cap_value * 1000
        index = index + 1
    else:
        pass
    if cap_value.is_integer():
        cap_value = int(cap_value)
    value = str(cap_value) + cap_unit_ls[index] + cap_other
    return value

# a = '22nF/100V'
# print(convert_capacitance_unit(a))

def deal_res_value(res_value):
    pattern = r'^([\d.]+[r, R, k, K, m, M]+).*'  # 匹配以数字和字母组成的部分
    match = re.match(pattern, res_value)
    if match:
        return match.group(1)
    else:
        print(res_value)
        return None

# 电容电阻与其他value值标准化函数
def cap_res_normalize_value(reference, value):
    # 把要焊接的器件的/OPEN去掉
    if 'open' in value or 'Open' in value:
        value = value.replace('open', 'OPEN')
        value = value.replace('Open', 'OPEN')
    value = value.replace('/OPEN', '')
    if value.startswith('OPEN'):  # 跳过不焊接的器件
        return value
    # 根据位号判断是不是电容, 将容值改为uF, mF这种形式
    if re.match(r'^C\d+$', reference):
        value = value.replace(" ", "")  # 去除空格
        value = value.replace(',', '/')
        value = value.replace('，', '/')
        value = value.lower() # 先转为小写
        value = value.replace('f', 'F')
        value = value.replace('v', 'V')
        if 'F' not in value:
            value = value.replace('m', 'mF')
            value = value.replace('u', 'uF')
            value = value.replace('n', 'nF')
            value = value.replace('p', 'pF')
            value = value.replace('f', 'fF')
        value = convert_capacitance_unit(value)
    # 判断位号是不是电阻, 将Ω去掉，将r改为R，将K改为k
    elif re.match(r'^R\d+$', reference):
        value = value.replace(" ", "")  # 去除空格
        value = value.replace('Ω', '')
        value = value.replace('r', 'R')
        value = value.replace('K', 'k')
        value = value.replace(',', '/')
        value = value.replace('，', '/')
        value = deal_res_value(value)
    return value

# %%
# 创建一个字典，用于存储元器件信息
components = {}
item = 1
# 从表头行往下开始，遍历每一行数据
for row in origin_sheet.iter_rows(min_row=bom_components_begin_row_num, values_only=True):
    if any(row):  # 判断整行是否存在非空值，为空则跳过
        reference = str(row[2]).strip() # 元器件位号
        value     = str(row[3]).strip() # 元器件的Part
        footprint = str(row[4]).strip() # 元器件的PCB Footprint
        mpn       = str(row[5]).strip() if row[5] else None # 元器件的制造商编号

        value = cap_res_normalize_value(reference, value)
        # 将 value 和 footprint 转换为小写字符串，用于比较
        value_lower = value.lower()
        footprint_lower = footprint.lower()

        # if any((value_lower == comp[0].lower() and footprint_lower == comp[1].lower()) for comp in components):
        # 如果元器件已存在，则将当前行的位号添加到已存在的元器件行的位号列中
        comp_is_not_exist = True
        for key, comp in components.items():
            if value_lower == comp['Part'].lower() and footprint_lower == comp['PCB Footprint'].lower():
                comp['Reference'].append(reference)
                comp['Quantity'] += 1
                if mpn not in comp['MPN']:
                    comp['MPN'].append(mpn)
                comp_is_not_exist = False
                break
        if comp_is_not_exist:
            # 如果不存在，则创建一个新的元器件行
            components[(value, footprint)] = {
                'Item' : item,
                'Quantity' : 1,
                'Reference': [reference],
                'Part': value,
                'PCB Footprint': footprint,
                'MPN': [mpn]
            }
            item += 1

# %%
# 删除复制来的原始工作表
workbook.remove(origin_sheet)

# 创建一个新的工作表，用于存储合并后的数据
sheet02 = workbook.create_sheet('sheet02')

# 写入表头
sheet02.append(std_bom_header)

# 写入合并后的数据
for component in components.values():
    # 去除component['MPN']中的None
    new_mpn_ls = [x for x in component['MPN'] if x is not None]
    sheet02.append([
        component['Item'],
        component['Quantity'],
        ', '.join(component['Reference']) if component['Reference'] else '',
        component['Part'],
        component['PCB Footprint'],
        ', '.join(new_mpn_ls) if new_mpn_ls else '',
    ])

# %%
# 插入焊接器件与不焊接器件的间隔
Non_soldered_components = '————————————————————以下器件不焊接————————————————————'
sheet02.append([Non_soldered_components])

# 遍历新的表格，将Part列中以open开始的行剪切到表格最后
# 获取 Part 列的列号
part_column = std_bom_header.index("Part") + 1
# 记录要剪切的行号
rows_to_cut = []

# 遍历 Part 列，记录满足条件的行号
for row in range(1, sheet02.max_row + 1):
    cell_value = sheet02.cell(row=row, column=part_column).value
    if (type(cell_value) == str) and cell_value.lower().startswith('open'):
        rows_to_cut.append(row)

# 将满足条件的行剪切到表格的最后
for row in rows_to_cut:
    # move_range(某行，向下移动的行数)
    sheet02.move_range(f"A{row}:Z{row}", rows = sheet02.max_row - row + 1)


# 删除所有空行
for row in sheet02.iter_rows(max_col=len(std_bom_header)):
    if any(row) == False:
        sheet02.delete_rows(row)

# %%
# 创建一个新的工作表，用于存储移除空行后的数据，且重排一下序号
sheet03 = workbook.create_sheet('sheet03')
item = 1
for row in sheet02.iter_rows(min_row=1, values_only=True):
    if any(row):  # 判断整行是否存在非空值，为空则跳过
        if isinstance(row[0], int):
            sheet03.append([item] + list(row[1:]))
            item += 1
        elif row[0] == Non_soldered_components:
            sheet03.append(list(row))
            # 合并前 len(std_bom_header) 列
            merge_range = f"A{sheet03.max_row}:{openpyxl.utils.get_column_letter(len(std_bom_header))}{sheet03.max_row}"
            sheet03.merge_cells(merge_range)
            # 合并单元格，合并后会自动保留合并首列的值
            merged_cell = sheet03[merge_range]
            merged_cell[0][0].fill = openpyxl.styles.PatternFill(fill_type='solid', fgColor='FFFF00')  # 亮黄色
            item = 1
        elif isinstance(row[0], str):
            sheet03.append(list(row))
            item = 1

# 上一个表用完删除
workbook.remove(sheet02)

# %%
# 此单元生成最终表格并设置表格样式
# 创造最终sheet
final_sheet = workbook.copy_worksheet(sheet03)
final_sheet.title = final_sheet_title
# 上一个表用完删除
workbook.remove(sheet03)

# 设置字体样式和大小
font = openpyxl.styles.Font(name='Consolas', size=14)

# 设置文本对齐和自动换行
alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center', wrap_text=True)

# 遍历单元格，并应用格式，并给所有单元格加上边框
for row in final_sheet.iter_rows(max_col=len(std_bom_header)):
    for cell in row:
        cell.font = font
        cell.alignment = alignment
        cell.border = openpyxl.styles.Border(
            left   = openpyxl.styles.Side (style = 'thin'),
            right  = openpyxl.styles.Side (style = 'thin'),
            top    = openpyxl.styles.Side (style = 'thin'),
            bottom = openpyxl.styles.Side (style = 'thin')
        )

# 设置列宽 依据std_bom_header = ('Item', 'Quantity', 'Reference', 'Part', 'PCB Footprint', 'MPN')
final_sheet.column_dimensions[openpyxl.utils.get_column_letter (std_bom_header.index ("Item"         )+ 1)].width = 8
final_sheet.column_dimensions[openpyxl.utils.get_column_letter (std_bom_header.index ("Quantity"     )+ 1)].width = 8
final_sheet.column_dimensions[openpyxl.utils.get_column_letter (std_bom_header.index ("Reference"    )+ 1)].width = 50
final_sheet.column_dimensions[openpyxl.utils.get_column_letter (std_bom_header.index ("Part"         )+ 1)].width = 30
final_sheet.column_dimensions[openpyxl.utils.get_column_letter (std_bom_header.index ("PCB Footprint")+ 1)].width = 30
final_sheet.column_dimensions[openpyxl.utils.get_column_letter (std_bom_header.index ("MPN"          )+ 1)].width = 50


# %%
# 保存并关闭Excel文件
workbook.save(final_bom_name)
workbook.close()

pyautogui.alert(f'新BOM\n{final_bom_name}\n创建成功!', '确认')

# 调用 Excel 打开新创建的文件
os.startfile(final_bom_name)
